package com.demodis.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.demodis.dao.DetContentDaoI;
import com.demodis.domain.TblDetContent;
import com.demodis.util.DBUtil;
import com.demodis.util.SystemConstant;

public class DetContentDaoImpl extends BaseDao implements DetContentDaoI {

	@Override
	public boolean save(TblDetContent t) {
		String sql = "INSERT INTO TBL_DETCONTENT(posId, projectName, detContent, detproOption) VALUES(?, ?, ?, ?);";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, t.getPosId());
			ps.setString(2, t.getProjectName());
			ps.setString(3, t.getDetContent());
			ps.setString(4, t.getDetproOption());

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1 ? true : false;
	}

	@Override
	public boolean delete(TblDetContent t) {
		String sql = "DELETE FROM TBL_DETCONTENT WHERE contentId IN(" + t.getIds() + ");";
		int i = 0;
		try {
			conn = DBUtil.getConnection();

			ps = conn.prepareStatement(sql);
			i = ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i > 0 ? true : false;
	}

	@Override
	public boolean modify(TblDetContent t) {
		String sql = "UPDATE TBL_DETCONTENT SET posId = ?, projectName = ?, detContent = ?, detproOption = ? WHERE contentId = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, t.getPosId());
			ps.setString(2, t.getProjectName());
			ps.setString(3, t.getDetContent());
			ps.setString(4, t.getDetproOption());
			ps.setInt(5, t.getContentId());

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}

		return i == 1;
	}

	@Override
	public List<TblDetContent> get() {
		String sql = "SELECT A.*, B.POSNAME, b.posType FROM TBL_DETCONTENT A, TBL_POSITION B WHERE A.POSID = B.POSID;";
		List<TblDetContent> list = new ArrayList<TblDetContent>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				TblDetContent detCont = new TblDetContent();
				
				detCont.setContentId(rs.getInt("contentId"));
				detCont.setPosId(rs.getInt("posId"));
				detCont.setPosName(rs.getString("posName"));
				detCont.setPosType(rs.getInt("posType"));
				detCont.setProjectName(rs.getString("projectName"));
				detCont.setDetContent(rs.getString("detContent"));
				detCont.setDetproOption(rs.getString("detproOption"));
				list.add(detCont);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public List<TblDetContent> querySDC(TblDetContent t) {
		String sql = "SELECT A.*, B.POSNAME FROM TBL_DETCONTENT A, TBL_POSITION B, tbl_deterperson c WHERE A.POSID = B.POSID and b.posId = c.posId and c.userId = ? ORDER BY A.PROJECTNAME;";
		List<TblDetContent> list = new ArrayList<TblDetContent>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, t.getUserId());
			rs = ps.executeQuery();
			while (rs.next()) {
				TblDetContent detCont = new TblDetContent();
				
				detCont.setContentId(rs.getInt("contentId"));
				detCont.setPosId(rs.getInt("posId"));
				detCont.setPosName(rs.getString("posName"));
				detCont.setProjectName(rs.getString("projectName"));
				detCont.setDetContent(rs.getString("detContent"));
				detCont.setDetproOption(rs.getString("detproOption"));
				list.add(detCont);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public double getPresidentAndSecretariatAppraise(int userId) {
		String sql = "SELECT ROUND(SUM(a.score)/COUNT(*), 4) score FROM tbl_deterpersonresult a, tbl_deterperson b WHERE a.doDeterUserId = b.userId AND a.userId = ? AND b.posId = ?;";
		double score = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setInt(2, SystemConstant.PRESIDENTANDSECRETARIATPOSID);
			rs = ps.executeQuery();
			rs.next();
			score = rs.getDouble("score");
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return score;
	}

	@Override
	public double getChargeGroupEmployeeAppraiseResult(int userId) {
		String sql = "SELECT ROUND(SUM(b.score)/COUNT(*), 4) score FROM (SELECT b.userId FROM (SELECT b.groupId FROM tbl_user a, tbl_group b WHERE a.userId = b.groupLeaderId AND b.groupLeaderId = ?) a LEFT JOIN tbl_user b ON a.groupId = b.groupId) a, tbl_deterpersonresult b WHERE a.userId = b.doDeterUserId;";
		double score = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			rs = ps.executeQuery();
			rs.next();
			score = rs.getDouble("score");
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return score;
	}

	@Override
	public double getAllTwoLevelLeaderAppraiseResult(int userId) {
		String sql = "SELECT ROUND(SUM(a.score)/COUNT(*), 4) score FROM tbl_deterpersonresult a, tbl_deterperson b WHERE a.doDeterUserId = b.userId AND a.userId = ? AND b.posId = ?;";
		double score = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setInt(2, SystemConstant.TWOLEVELLEADERPOSID);
			rs = ps.executeQuery();
			rs.next();
			score = rs.getDouble("score");
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return score;
	}

	@Override
	public double getZhijiGroupScore(int groupId) {
		String sql = "SELECT score FROM tbl_group WHERE groupId = ?;";
		double score = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, groupId);
			rs = ps.executeQuery();
			rs.next();
			score = rs.getDouble("score");
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return score;
	}

	@Override
	public double getAllFenguanLeaderScore(int userId) {
		String sql = "SELECT ROUND(SUM(score)/COUNT(*), 4) score FROM tbl_deterpersonresult WHERE userId = ? AND doDeterUserId IN (SELECT userId FROM tbl_deterperson WHERE posId = ?);";
		double score = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setInt(2, SystemConstant.SCHOOLLEADERPOSID);
			rs = ps.executeQuery();
			rs.next();
			score = rs.getDouble("score");
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return score;
	}

	@Override
	public double getZhijiGroupEmployeeAppraiseResult(int userId, int groupId) {
		String sql = "SELECT ROUND(SUM(score)/COUNT(*), 4) score FROM tbl_user a, tbl_deterpersonresult b WHERE a.groupId = ? AND a.userId = b.doDeterUserId AND b.userId = ?;";
		double score = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, groupId);
			ps.setInt(2, userId);
			rs = ps.executeQuery();
			rs.next();
			score = rs.getDouble("score");
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return score;
	}

	@Override
	public double getDeterScore(int userId) {
		String sql = "SELECT ROUND(SUM(score)/COUNT(*), 4) score FROM tbl_deterpersonresult WHERE userId = ?;";
		double score = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			rs = ps.executeQuery();
			rs.next();
			score = rs.getDouble("score");
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return score;
	}

	@Override
	public Map<String, Double> getortherScore(int userId) {
		String sql = "SELECT * FROM tbl_groupempapp WHERE userId = ?;";
		Map<String, Double> map = new HashMap<String, Double>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			rs = ps.executeQuery();
			if(rs.next()) {
				map.put("groupAppraise", rs.getDouble("groupAppraise"));
				map.put("reciprocalAppraise", rs.getDouble("reciprocalAppraise"));
				map.put("firstGroupAppraise", rs.getDouble("firstGroupAppraise"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return map;
	}

}
